package com.cx.dbtablelist;

import java.awt.Point;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;

import com.cx.dbtablelist.db.DBUtils;
import com.cx.dbtablelist.excel.ExcelUtils;
import com.cx.dbtablelist.util.ExpressionUtils;

/**
 * 根据相关信息生成TableList的信息
 * @date : 2014-1-15
 * @author caoxu-yiyang@qq.com
 */
public class ExcuteTableListExcel {
	private final Map<Integer, String> columnIndex = new HashMap<Integer, String>();
	private final Map<Point, String> tableInfoIndex = new HashMap<Point, String>();
	/**
	 * 复制的sheet
	 * */
	private int copySheetIndex = Integer.valueOf(ProjectConfig.get("TEMPLATE_SHEET"))-1;
	/**
	 * sheet中开始重复的行
	 * */
	private int dulStartRow = Integer.valueOf(ProjectConfig.get("DUL_ROW_NUM"))-1;
	
	public void generateTableListExcel() throws IOException, SQLException{
		String readFileName = ProjectConfig.get("READ_FILE");
		Workbook workbook = ExcelUtils.readFile(readFileName);
		Sheet copySheet = workbook.getSheetAt(copySheetIndex);
		boolean sheetNameIsExpression = ExpressionUtils.isExpression(copySheet.getSheetName());
		String sheetNameNotation = ExpressionUtils.tryGetExpressionValue(copySheet.getSheetName());
		
		analyseTableInfoIndex(copySheet,dulStartRow);
		analyseColumnIndex(copySheet,dulStartRow);
		
		
		
		ResultSet tables = DBUtils.getTables();
		//循环遍历所有表
		while(tables.next()){
			Sheet newSheet = workbook.cloneSheet(copySheetIndex);
			String tableName = null;
			if(sheetNameIsExpression){
				tableName = tables.getString(sheetNameNotation);
			}
			//设置sheet的名称为表名
			if(tableName != null){
				String newSheetName = WorkbookUtil.createSafeSheetName(tableName);
				if(workbook.getSheet(newSheetName) == null){
					workbook.setSheetName(workbook.getNumberOfSheets()-1, newSheetName);
				}
			}
			
			//给表头赋值
			Set<Point> tableInfoIndexKeys = this.tableInfoIndex.keySet();
			for (Point point : tableInfoIndexKeys) {
				String pointMapValue = this.tableInfoIndex.get(point);
				newSheet.getRow(point.x).getCell(point.y).setCellValue(tables.getString(pointMapValue));
			}
			
			fillContentValues(DBUtils.getTableInfo(tables),newSheet, copySheet.getRow(dulStartRow));
		}
		ExcelUtils.writeFile(workbook, ProjectConfig.get("WRITE_FILE"));
	}

	/**
	 * 填充内容区域的值
	 * @author caoxu-yiyang@qq.com
	 * @param tableInfos
	 * @param sheet
	 * @param styleRow 
	 * @throws SQLException
	 */
	private void fillContentValues(ResultSet tableInfos, Sheet sheet, Row styleRow) throws SQLException{
		int i = 0;
		Set<Integer> columnIndexKeys = this.columnIndex.keySet();
		Row newRow = null;
		boolean tableInfosIsEmpty = !tableInfos.next(); //没有结果时将参数行置为空
		do {
			newRow = sheet.createRow(dulStartRow+i);
			int lastColumn = styleRow.getPhysicalNumberOfCells();
			for (int j = 0; j <= lastColumn; j++) {
				Cell cell = newRow.createCell(j);
				if(columnIndexKeys.contains(j)){
					cell.setCellValue(tableInfosIsEmpty ? null :tableInfos.getString(this.columnIndex.get(j)));
				}
				
				Cell copy = styleRow.getCell(j);
				if(copy != null){
					cell.setCellStyle(copy.getCellStyle());
				}
			}
			i++;
		}while (tableInfos.next());
		
	}
	
	/**
	 * 分析表头部分的信息
	 * @author caoxu-yiyang@qq.com
	 * @param sheet
	 * @param endRow 表头最后一行
	 */
	private void analyseTableInfoIndex(Sheet sheet, int endRow) {
		Row row = null;
		Cell cell = null;
		for (int i = 0; i < endRow; i++) {
			row = sheet.getRow(i);
			if(row != null){
				for (int j = 0; j < row.getLastCellNum(); j++) {
					cell = row.getCell(j);
					if(cell != null){
						String cellValue = cell.getStringCellValue();
						if(ExpressionUtils.isExpression(cellValue)){
							this.tableInfoIndex.put(new Point(i, j), ExpressionUtils.getExpressionValue(cellValue));
						}
					}
				}
			}
		}
	}

	/**
	 * 分析内容行中每个列对应的数据库列名
	 * @author caoxu-yiyang@qq.com
	 * @param sheet
	 * @param dulStartRow
	 */
	private void analyseColumnIndex(Sheet sheet, Integer dulStartRow) {
		int maxRow = sheet.getPhysicalNumberOfRows();
		for (int r = dulStartRow; r < maxRow; r++) {
			Row row = sheet.getRow(r);
			if(row != null){
				for (Cell cell : row) {
					String cellValue = cell.getStringCellValue();
					if(ExpressionUtils.isExpression(cellValue)){
						String column = ExpressionUtils.getExpressionValue(cellValue);
						this.columnIndex.put(cell.getColumnIndex(), column);
					}
				}
			}
		}
	}
}
